﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace QuanLyHocBongSinhVien.DAO
{
    class SinhVIen_DAO
    {
        dbConnection db = new dbConnection();

        public DataTable getAllSinhVien()
        {
            return db.ExecuteQuery("select * from SINHVIEN");
        }

        public DataTable getMaSV()
        {
            string sql = "select MASV from SINHVIEN";
           return db.ExecuteQuery(sql);
        }
        public void ThemSV(string m, string t, DateTime ns, string gt, string l, float d, float drl, string mk)
        {
            SqlParameter[] mang = new SqlParameter[8];
            mang[0] = new SqlParameter("@masv",m);
            mang[1] = new SqlParameter("@hoten", t);
            mang[2] = new SqlParameter("@ngaysinh", ns);
            mang[3] = new SqlParameter("@gioitinh", gt);
            mang[4] = new SqlParameter("@lop", l);
            mang[5] = new SqlParameter("@diemtb", d);
            mang[6] = new SqlParameter("@diemrl", drl);
            mang[7] = new SqlParameter("@makhoa", mk);
            db.ExecuteNonQuery("insertSinhVien", mang);
        }

        public void SuaSV(string m, string t, DateTime ns, string gt, string l, float d, float drl, string mk)
        {
            SqlParameter[] mang = new SqlParameter[8];
            mang[0] = new SqlParameter("@masv", m);
            mang[1] = new SqlParameter("@hoten", t);
            mang[2] = new SqlParameter("@ngaysinh", ns);
            mang[3] = new SqlParameter("@gioitinh", gt);
            mang[4] = new SqlParameter("@lop", l);
            mang[5] = new SqlParameter("@diemtb", d);
            mang[6] = new SqlParameter("@diemrl", drl);
            mang[7] = new SqlParameter("@makhoa", mk);
            db.ExecuteNonQuery("updateSinhVien", mang);
        }

        public void XoaSV(string ma)
        {
            SqlParameter[] mang = new SqlParameter[1];
            mang[0] = new SqlParameter("@masv", ma);
            db.ExecuteNonQuery("Xoa_SinhVien",mang);
        }

        public void TimKiemMaSV(string ma)
        {
            SqlParameter[] mang = new SqlParameter[1];
            mang[0] = new SqlParameter("@masv", ma);
            db.ExecuteNonQuery("TimKiem_SinhVien", mang);
        }
        public void TimKiemTenSV(string ma)
        {
            SqlParameter[] mang = new SqlParameter[1];
            mang[0] = new SqlParameter("@tensv", ma);
            db.ExecuteNonQuery("TimKiem_TenSinhVien", mang);
        }

        public void TimKiemLopSV(string ma)
        {
            SqlParameter[] mang = new SqlParameter[1];
            mang[0] = new SqlParameter(" @lop", ma);
            db.ExecuteNonQuery("TimKiem_LopSinhVien", mang);
        }

        public void TimKiemMaKhoaSV(string ma)
        {
            SqlParameter[] mang = new SqlParameter[1];
            mang[0] = new SqlParameter("  @makhoa", ma);
            db.ExecuteNonQuery("TimKiem_MaKhoa", mang);
        }
        public void TimKiemDiemTBSV(float ma)
        {
            SqlParameter[] mang = new SqlParameter[1];
            mang[0] = new SqlParameter("@diemtb ", ma);
            db.ExecuteNonQuery("TimKiem_DTBSinhVien", mang);
        }

        public void TimKiemDiemRLSV(float ma)
        {
            SqlParameter[] mang = new SqlParameter[1];
            mang[0] = new SqlParameter("@diemrl ", ma);
            db.ExecuteNonQuery("TimKiem_DRLSinhVien", mang);
        }
    }
}
